Source code for src.db

import os, sys, re, MySQLdb
from os.path import normpath, join
from sys import path
from contextlib import closing

import cardsharp as cs

from configuration import db_info

def create_db(db, drop_db=True):
    """Create an output relational database. Will drop existing database unless the drop_db parameter is set to False.
    @param db: Dictionary containing the database metadata including the database name and connection parameters.
    @param drop_db: If set to True will cause the database to be dropped if it already exists, default is True.
    """
    with closing(MySQLdb.connect(user=db['user'], passwd=db['pass'])) as cnx:
        with closing(cnx.cursor()) as c:
            c.execute('show databases')
            response = c.fetchall()
            if db['name'] not in [db_list[0] for db_list in response]:
                c.execute("""CREATE DATABASE %s CHARACTER SET utf8 COLLATE utf8_unicode_ci""" % db['name'])
            elif drop_db:
                c.execute("DROP DATABASE %s" % db['name'])
                print """CREATE DATABASE %s CHARACTER SET utf8 COLLATE utf8_unicode_ci""" % db['name']
                c.execute("""CREATE DATABASE %s CHARACTER SET utf8 COLLATE utf8_unicode_ci""" % db['name'])

def create_lookups(db):
    """Helper function to add lookup tables to a database. 
    The lookup tables reside within a directory db_info['dir'], 
    each table is stored as a file in the lookup directory with the filename 
    being the name of the table."""
    
    for file in os.listdir(join(db['dir'], 'lookups')):
        try:
            if re.match('[a-zA-Z0-9]+\.xls', file): 
                name, ext = file.split('.')
            
        except ValueError:
            print (''.join(["ValueError: too many values to unpack.", " Make sure all your files are closed."]))
            sys.exit(1)
            
        ext = '.' + ext
        table = cs.load(source=join(db['dir'], 'lookups', file), format='excel')

        table['id'].convert('integer')
        table['id'].flags.primary_key = 'id'
        
        cs.wait()

        table.save(source=db['name'], format='mysql', dataset=name,
                   user = db['user'], pwd = db['pass'], 
                   overwrite=True)
    cs.wait()